import os
import django
import datetime

# 设置Django环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'ai_teach.settings')
django.setup()

from django.db import connection

def create_study_plan_table():
    """创建学习计划表"""
    with connection.cursor() as cursor:
        # 检查表是否已存在
        cursor.execute("""
            SELECT COUNT(*) 
            FROM information_schema.tables 
            WHERE table_schema = DATABASE() 
            AND table_name = 'student_study_plan'
        """)
        table_exists = cursor.fetchone()[0]
        
        if table_exists:
            print("表student_study_plan已存在，跳过创建")
            return
        
        # 创建表
        print("创建student_study_plan表...")
        cursor.execute("""
            CREATE TABLE `student_study_plan` (
              `plan_id` int NOT NULL AUTO_INCREMENT COMMENT '计划ID',
              `stu_id` int NOT NULL COMMENT '学生ID',
              `plan_name` varchar(255) NOT NULL COMMENT '计划名称',
              `plan_date` date NOT NULL COMMENT '计划日期',
              `plan_type` varchar(50) NOT NULL DEFAULT 'single' COMMENT '计划类型',
              `start_time` time NOT NULL COMMENT '开始时间',
              `end_time` time NOT NULL COMMENT '结束时间',
              `plan_content` text NOT NULL COMMENT '计划内容',
              `cycle_value` int DEFAULT NULL COMMENT '周期值',
              `cycle_unit` varchar(20) DEFAULT NULL COMMENT '周期单位',
              `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
              `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
              PRIMARY KEY (`plan_id`),
              KEY `idx_stu_id` (`stu_id`),
              KEY `idx_plan_date` (`plan_date`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生学习计划表'
        """)
        print("表创建成功！")

def add_test_data():
    """添加测试数据"""
    with connection.cursor() as cursor:
        # 检查用户是否存在
        cursor.execute("SELECT id FROM student_user WHERE username = '2116411017'")
        user_result = cursor.fetchone()
        
        if not user_result:
            print("创建测试用户2116411017...")
            cursor.execute("""
                INSERT INTO student_user (username, password, email, status, create_time, update_time)
                VALUES ('2116411017', '123456', '2116411017@example.com', 1, NOW(), NOW())
            """)
            cursor.execute("SELECT id FROM student_user WHERE username = '2116411017'")
            user_result = cursor.fetchone()
        
        user_id = user_result[0]
        print(f"用户ID: {user_id}")
        
        # 清除已有数据
        print("清除该用户已有计划数据...")
        cursor.execute("DELETE FROM student_study_plan WHERE stu_id = %s", [user_id])
        
        # 今天的日期
        today = datetime.date.today()
        
        # 插入测试数据
        print("插入单次计划测试数据...")
        cursor.execute("""
            INSERT INTO student_study_plan 
            (stu_id, plan_name, plan_date, plan_type, start_time, end_time, plan_content, created_at, updated_at)
            VALUES
            (%s, '数据结构复习', %s, 'single', '08:30:00', '10:30:00', '复习二叉树和图论相关知识点，完成课后习题1-5', NOW(), NOW()),
            (%s, '算法设计作业', %s, 'single', '14:00:00', '16:00:00', '完成动态规划算法设计作业，提交到教学平台', NOW(), NOW()),
            (%s, '英语四级单词', %s, 'single', '19:30:00', '20:30:00', '背诵Unit 5的核心词汇，复习上周学过的单词', NOW(), NOW())
        """, [
            user_id, today, 
            user_id, today, 
            user_id, today
        ])
        
        # 明天和后天
        tomorrow = today + datetime.timedelta(days=1)
        day_after = today + datetime.timedelta(days=2)
        
        cursor.execute("""
            INSERT INTO student_study_plan 
            (stu_id, plan_name, plan_date, plan_type, start_time, end_time, plan_content, created_at, updated_at)
            VALUES
            (%s, '数据库实验', %s, 'single', '09:00:00', '11:30:00', '完成数据库SQL查询优化实验，撰写实验报告', NOW(), NOW()),
            (%s, '毕业设计讨论', %s, 'single', '13:30:00', '15:00:00', '与导师讨论毕业设计进展，准备下一阶段工作计划', NOW(), NOW()),
            (%s, '软件工程小组会议', %s, 'single', '10:00:00', '12:00:00', '讨论项目进展，分配下一阶段任务，解决当前遇到的问题', NOW(), NOW()),
            (%s, '编译原理预习', %s, 'single', '15:30:00', '17:30:00', '预习词法分析和语法分析章节，阅读教材第3章', NOW(), NOW())
        """, [
            user_id, tomorrow,
            user_id, tomorrow,
            user_id, day_after,
            user_id, day_after
        ])
        
        print("插入周期计划测试数据...")
        cursor.execute("""
            INSERT INTO student_study_plan 
            (stu_id, plan_name, plan_date, plan_type, start_time, end_time, plan_content, cycle_value, cycle_unit, created_at, updated_at)
            VALUES
            (%s, '晨跑锻炼', %s, 'periodic', '06:30:00', '07:15:00', '晨跑30分钟，保持身体健康和良好的精神状态', 1, 'days', NOW(), NOW()),
            (%s, '英语口语练习', %s, 'periodic', '21:00:00', '21:30:00', '使用英语学习软件进行口语训练，每次30分钟', 2, 'days', NOW(), NOW()),
            (%s, '算法能力训练', %s, 'periodic', '16:00:00', '17:30:00', '在LeetCode上完成2-3道算法题，提升编程能力', 3, 'days', NOW(), NOW()),
            (%s, '专业论文阅读', %s, 'periodic', '19:00:00', '20:30:00', '阅读计算机科学领域的学术论文，了解最新研究动态', 1, 'weeks', NOW(), NOW())
        """, [
            user_id, today,
            user_id, today,
            user_id, tomorrow,
            user_id, day_after
        ])
        
        # 验证数据
        cursor.execute("SELECT COUNT(*) FROM student_study_plan WHERE stu_id = %s", [user_id])
        count = cursor.fetchone()[0]
        print(f"成功插入 {count} 条计划数据")

        # 使用用户名作为stu_id值插入一些数据
        print("插入以用户名作为stu_id的测试数据...")
        cursor.execute("SELECT username FROM student_user WHERE id = %s", [user_id])
        username = cursor.fetchone()[0]
        
        cursor.execute("""
            INSERT INTO student_study_plan 
            (stu_id, plan_name, plan_date, plan_type, start_time, end_time, plan_content, created_at, updated_at)
            VALUES
            (%s, '使用用户名的计划', %s, 'single', '12:00:00', '13:00:00', '这是一个以用户名作为stu_id的测试计划', NOW(), NOW())
        """, [
            username, today
        ])

if __name__ == "__main__":
    create_study_plan_table()
    add_test_data()
    print("操作完成！") 